﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;

namespace SiparisKontrol
{
    public class DbSqlServer
    {
        public string CnnString { get; set; }
        public DbSqlServer()
        {
            this.CnnString = GetString();
        }
        private string GetString()
        {
            return string.Format("Data Source={0};User Id={1};Password={2};Database={3}", Program.Session[Settings.ServerName], Program.Session[Settings.UserName], Program.Session[Settings.Password], Program.Session[Settings.DatabaseName]);
        }
        public DataTable Fill(string sqlText, SqlTransaction trn, params SqlParameter[] prm)
        {
            DataTable dt = new DataTable();
            using (SqlCommand cmd = new SqlCommand(sqlText, trn.Connection))
            {
                cmd.Transaction = trn;
                if (prm != null)
                    foreach (SqlParameter p in prm)
                    {
                        cmd.Parameters.Add(p);
                    }
                using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                {
                    adp.Fill(dt);
                    cmd.Parameters.Clear();
                }
            }
            return dt;
        }
        public DataTable Fill(string sqlText, params SqlParameter[] prm)
        {
            DataTable dt = new DataTable();
            using (SqlConnection cn = new SqlConnection(this.CnnString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlText, cn))
                    {
                        if (prm != null)
                            foreach (SqlParameter p in prm)
                            {
                                cmd.Parameters.Add(p);
                            }
                        using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                        {
                            adp.Fill(dt);
                            cmd.Parameters.Clear();
                        }
                    }
                }
                catch (SqlException ex)
                {
                    Logging.GetLog.ErrorLog(ex);
                }
                finally { if (cn.State == ConnectionState.Open) cn.Close(); }
            }
            return dt;
        }
        public DataTable Fill(string sqlText)
        {
            DataTable dt = new DataTable();
            using (SqlConnection cn = new SqlConnection(this.CnnString))
            {
                try
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlText, cn))
                    {
                        using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                        {
                            adp.Fill(dt);
                            cmd.Parameters.Clear();
                        }
                    }
                }
                catch (SqlException ex)
                {
                    Logging.GetLog.ErrorLog(ex);
                }
                finally { if (cn.State == ConnectionState.Open) cn.Close(); }
            }
            return dt;
        }

        object DefaultValue(string type)
        {
            switch (type)
            {
                case "System.String":
                    return string.Empty;
                case "System.Int32":
                    return 0;
                case "System.Int16":
                    return Convert.ToInt16(0);
                case "System.Int64":
                    return Convert.ToInt64(0);
                case "System.DateTime":
                    return DateTime.Now;
                case "System.Decimal":
                    return Convert.ToDecimal(0);
                case "System.Boolean":
                    return false;
                case "System.Double":
                    return Convert.ToDouble(0);
                case "System.Single":
                    return Convert.ToSingle(0);
                case "System.Byte":
                    return Convert.ToByte(0);
                default:
                    return null;
            }

        }
        public List<T> ToList<T>(T sample, string sqlText, params SqlParameter[] prm)
        {
            List<T> fdList = new List<T>();
            try
            {
                string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
                using (SqlConnection cn = new SqlConnection(this.CnnString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlText, cn))
                    {
                        if (prm != null)
                            foreach (SqlParameter p in prm)
                                cmd.Parameters.Add(p);

                        SqlDataReader rd = cmd.ExecuteReader();
                        var properties = TypeDescriptor.GetProperties(sample);
                        Type[] types = new Type[properties.Count];
                        for (int i = 0; i < properties.Count; i++)
                        {
                            types[i] = properties[i].PropertyType;
                        }

                        ConstructorInfo ctor = sample.GetType().GetConstructor(types);
                        object[] objArray = new object[properties.Count];

                        while (rd.Read())
                        {
                            for (int i = 0; i < rd.FieldCount; i++)
                            {
                                objArray[i] = rd.GetValue(i);
                                if (objArray[i] == DBNull.Value)
                                    objArray[i] = DefaultValue(rd.GetFieldType(i).ToString());
                            }
                            if (ctor != null)
                            {
                                T tmp = (T)ctor.Invoke(objArray);
                                fdList.Add(tmp);
                            }
                        }
                        rd.Close();
                    }
                    cn.Close();
                }
            }
            catch (Exception ex)
            {
                Logging.GetLog.ErrorLog(ex);
            }
            return fdList;
        }
        public List<T> ToList<T>(T sample, string sqlText, SqlTransaction trn, params SqlParameter[] prm)
        {
            List<T> fdList = new List<T>();
            try
            {
                string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
                using (SqlCommand cmd = new SqlCommand(sqlText, trn.Connection))
                {
                    cmd.Transaction = trn;

                    if (prm != null)
                        foreach (SqlParameter p in prm)
                            cmd.Parameters.Add(p);

                    SqlDataReader rd = cmd.ExecuteReader();
                    var properties = TypeDescriptor.GetProperties(sample);
                    Type[] types = new Type[properties.Count];
                    for (int i = 0; i < properties.Count; i++)
                    {
                        types[i] = properties[i].PropertyType;
                    }

                    ConstructorInfo ctor = sample.GetType().GetConstructor(types);
                    object[] objArray = new object[properties.Count];

                    while (rd.Read())
                    {
                        for (int i = 0; i < rd.FieldCount; i++)
                        {
                            objArray[i] = rd.GetValue(i);
                            if (objArray[i] == DBNull.Value)
                                objArray[i] = DefaultValue(rd.GetFieldType(i).ToString());
                        }
                        if (ctor != null)
                        {
                            T tmp = (T)ctor.Invoke(objArray);
                            fdList.Add(tmp);
                        }
                    }
                    rd.Close();
                }
            }
            catch (Exception ex)
            {
                Logging.GetLog.ErrorLog(ex);
            }
            return fdList;
        }

        public SqlDataReader List2(string sqlText)
        {
            SqlConnection cn = new SqlConnection(this.CnnString);
            try
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand(sqlText, cn);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException ex)
            {
                Logging.GetLog.ErrorLog(ex);
            }
            return null;
        }

        public T ExecScalar<T>(string sqlText)
        {
            object sonuc = null;
            try
            {
                using (SqlConnection cn = new SqlConnection(this.CnnString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlText, cn))
                    {
                        sonuc = cmd.ExecuteScalar();
                    }
                    cn.Close();
                }
                if (sonuc != DBNull.Value)
                    return (T)Convert.ChangeType(sonuc, typeof(T), null);
                else
                    return (T)Cevir.DefaultSetting(typeof(T).ToString());
            }
            catch (Exception ex)
            {
                Logging.GetLog.ErrorLog(ex);
            }
            return (T)Cevir.DefaultSetting(typeof(T).ToString());
        }
        public T ExecScalar<T>(string sqlText, params SqlParameter[] prm)
        {
            object sonuc = null;
            try
            {
                using (SqlConnection cn = new SqlConnection(this.CnnString))
                {
                    cn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlText, cn))
                    {
                        if (prm != null)
                            foreach (SqlParameter p in prm)
                            {
                                cmd.Parameters.Add(p);
                            }
                        sonuc = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                    }
                    cn.Close();
                }

                if (sonuc != DBNull.Value)
                    return (T)Convert.ChangeType(sonuc, typeof(T), null);
                else
                    return (T)Cevir.DefaultSetting(typeof(T).ToString());
            }
            catch (Exception ex)
            {
                Logging.GetLog.ErrorLog(ex);
            }
            return (T)Cevir.DefaultSetting(typeof(T).ToString());
        }
        public T ExecScalar<T>(string sqlText, SqlTransaction trn)
        {
            object sonuc = null;
            try
            {
                using (SqlCommand cmd = new SqlCommand(sqlText, trn.Connection))
                {
                    cmd.Transaction = trn;
                    sonuc = cmd.ExecuteScalar();
                }
                if (sonuc != DBNull.Value)
                    return (T)Convert.ChangeType(sonuc, typeof(T), null);
                else
                    return (T)Cevir.DefaultSetting(typeof(T).ToString());
            }
            catch (Exception ex)
            {
                Logging.GetLog.ErrorLog(ex);
            }
            return (T)Cevir.DefaultSetting(typeof(T).ToString());
        }
        public T ExecScalar<T>(string sqlText, SqlTransaction trn, params SqlParameter[] prm)
        {
            object sonuc = null;
            try
            {
                using (SqlCommand cmd = new SqlCommand(sqlText, trn.Connection))
                {
                    cmd.Transaction = trn;
                    if (prm != null)
                        foreach (SqlParameter p in prm)
                        {
                            cmd.Parameters.Add(p);
                        }
                    sonuc = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                }
                if (sonuc != DBNull.Value)
                    return (T)Convert.ChangeType(sonuc, typeof(T), null);
                else
                    return (T)Cevir.DefaultSetting(typeof(T).ToString());
            }
            catch (Exception ex)
            {
                Logging.GetLog.ErrorLog(ex);
            }
            return (T)Cevir.DefaultSetting(typeof(T).ToString());
        }

        public SqlTransaction OpenTransaction()
        {
            try
            {
                SqlConnection cn = new SqlConnection(this.CnnString);
                cn.Open();
                return cn.BeginTransaction();
            }
            catch (Exception ex)
            {
                Logging.GetLog.ErrorLog(ex);
                return null;
            }
        }
        public void Exec(string sqlText, SqlTransaction trn, params SqlParameter[] prm)
        {
            try
            {
                if (trn != null)
                {
                    using (SqlCommand cmd = new SqlCommand(sqlText, trn.Connection))
                    {
                        cmd.Transaction = trn;
                        foreach (SqlParameter p in prm)
                            cmd.Parameters.Add(p);
                        cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                }
                else
                {
                    using (SqlConnection cn = new SqlConnection(this.CnnString))
                    {
                        cn.Open();
                        using (SqlCommand cmd = new SqlCommand(sqlText, cn))
                        {
                            foreach (SqlParameter p in prm)
                                cmd.Parameters.Add(p);
                            cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        cn.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
                Logging.GetLog.ErrorLog(ex);
            }
        }
        public void Exec(string sqlText, SqlTransaction trn)
        {
            try
            {
                if (trn != null)
                {
                    using (SqlCommand cmd = new SqlCommand(sqlText, trn.Connection))
                    {
                        cmd.Transaction = trn;
                        cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                }
                else
                {
                    using (SqlConnection cn = new SqlConnection(this.CnnString))
                    {
                        cn.Open();
                        using (SqlCommand cmd = new SqlCommand(sqlText, trn.Connection))
                        {
                            cmd.Transaction = trn;
                            cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        cn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                Logging.GetLog.ErrorLog(ex);
            }
        }
    }
}
